insert overwrite table tag_mid_people
select A.CARDUSER_ID,B.CUSTOMER_ID,B.customer_types,A.customer_level,B.gender,A.MOBILECARTYPE,A.NAME,B.BIRTHDAY,B.RECORDTIME,substr(B.MOBILEPHONE,1,11),B.ADDRESS,C.BALANCE/100
FROM TBL_CARDUSER AS A
JOIN TBL_CUSTOMER AS B
ON A.CUSTOMER_ID = B.CUSTOMER_ID
JOIN TBL_ACCOUNT AS C
ON C.CARDUSER_ID = A.CARDUSER_ID AND C.ACCOUNTTYPE = 2;

insert OVERWRITE table tag_people
select carduser_id,customer_id,customer_type,customer_level,gender,mobile_car_type,name,
case when
birthday between '1940-01-01' and '2000-01-01' then birthday
else null end ,
record_time,mobile_phone,address,balance
from tag_mid_people;

insert overwrite table tag_station
select * from
(
select CARDUSERID,STATIONCODE,COUNT,
row_number() over (partition by CARDUSERID order by COUNT DESC)AS rank from
(SELECT CARDUSERID,STATIONCODE,COUNT(ORDERDETAILS_ID) AS COUNT
FROM TBL_ORDERDETAILS
GROUP BY CARDUSERID,STATIONCODE) a
)b
where b.rank<=1;

insert overwrite table tag_stationb
select CARDUSERID,COUNT(distinct STATIONCODE)
from mid.TBL_ORDERDETAILS 
GROUP BY CARDUSERID ;

insert overwrite table tag_isdiscount
SELECT CARDUSERID,SUM(AMOUNT-REALAMOUNT)/100
FROM TBL_ORDERDETAILS
GROUP BY CARDUSERID;

insert  OVERWRITE table tag_mop
select * from
(
select carduser_id,item_description,
row_number() over(partition by carduser_id order by sum DESC )as rank
from
(select carduser_id,item_description,count(item_quantity)as count,sum(item_value)as sum
from transaction_items_mop_vip
group by carduser_id,item_description)a
)b where b.rank =1;

insert OVERWRITE table tag_oil
select * from
(
select CARDUSERID,OILCODE,
row_number() OVER(partition BY CARDUSERID ORDER BY COUNT DESC)AS rank
from
(select CARDUSERID,OILCODE,COUNT(ORDERDETAILS_ID)AS COUNT
FROM mid.TBL_ORDERDETAILS WHERE LITRE > 0
GROUP BY CARDUSERID,OILCODE)a
)b where b.rank = 1;

insert overwrite table tag_notoilDepartment
select * from
(
select d.carduser_id,d.dept_name,
row_number() over (partition by d.carduser_id order by d.count desc )as rank
from
(select A.carduser_id as carduser_id,C.dept_name as dept_name,COUNT(transaction_id)AS count
from transaction_items_notoil_vip as A
JOIN mid.products as B
on A.product_code = B.product_code and B.station_id = 50006
join mid.departments as C
on C.department_id = B.department_id and C.station_id=50006
group by A.carduser_id,C.dept_name)d
)e where e.rank = 1;

insert OVERWRITE table tag_historyDeal
select CARDUSERID,COUNT(ORDERDETAILS_ID),sum(REALAMOUNT)/100
from TBL_ORDERDETAILS
WHERE ORDER_DEAL = 4
GROUP BY CARDUSERID;

insert OVERWRITE table tag_oneMonthOil
select CARDUSERID,count(LITRE)as count,sum(REALAMOUNT)/100 as sum
from TBL_ORDERDETAILS
where SENDTIME > date_sub(CURRENT_DATE,30) and LITRE > 0
GROUP BY CARDUSERID;

insert OVERWRITE table tag_oneMonthNotoil
select CARDUSERID,count(LITRE)as count,sum(REALAMOUNT)/100 as sum
from TBL_ORDERDETAILS
where SENDTIME > date_sub(CURRENT_DATE,30) and LITRE = 0
GROUP BY CARDUSERID;

insert OVERWRITE table tag_threeMonthOil
select CARDUSERID,count(LITRE)as count,sum(REALAMOUNT)/100 as sum
from TBL_ORDERDETAILS
where SENDTIME > date_sub(CURRENT_DATE,90) and LITRE > 0
GROUP BY CARDUSERID;

insert OVERWRITE table tag_threeMonthNotoil
select CARDUSERID,count(LITRE)as count,sum(REALAMOUNT)/100 as sum
from TBL_ORDERDETAILS
where SENDTIME > date_sub(CURRENT_DATE,90) and LITRE = 0
GROUP BY CARDUSERID;

set hive.auto.convert.join=false;

insert OVERWRITE table tag_resulta
select A.carduser_id,A.customer_id,A.customer_type,A.customer_level,A.gender,A.mobile_car_type,A.name,A.birthday,A.record_time,L.lastdeal,A.mobile_phone,A.address,A.balance,B.station_id,C.sum_discount,D.max_mop,E.max_oil,F.max_department,G.oil_num,G.oil_money,H.notoil_num,H.notoil_money,I.oil_num,I.oil_money,J.notoil_num,J.notoil_money,k.deal_num,K.deal_money,M.max
from tag_people as A
left JOIN tag_station as B
on B.carduser_id = A.carduser_id
left join tag_isdiscount as C
on C.carduser_id = A.carduser_id
left join tag_mop as D
on D.carduser_id = A.carduser_id
left join tag_oil as E
on E.carduser_id = A.carduser_id
left join tag_notoilDepartment as F
on F.carduser_id = A.carduser_id
left join tag_oneMonthOil as G
on G.carduser_id = A.carduser_id
left join tag_oneMonthNotoil H
ON H.carduser_id = A.carduser_id
left join tag_threeMonthOil as I 
ON I.carduser_id = A.carduser_id
LEFT JOIN tag_threeMonthNotoil AS J
ON J.carduser_id = A.carduser_id
left join tag_historyDeal as K
on K.carduser_id = A.carduser_id
LEFT JOIN res_vipLastDeal as L
on L.CARDUSERID = A.carduser_id
LEFT JOIN tag_stationb as M 
on M.carduser_id = A.carduser_id;

set hive.auto.convert.join=true;


insert OVERWRITE table res_notoilSearch
select substr(B.transaction_date,1,10),A.station_id,A.product_code,A.item_description,SUM(A.item_quantity),SUM(A.item_value)
from mid.transaction_items_notoil as A
JOIN mid.transactions as B
ON B.transaction_id = A.transaction_id
where unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600
group by substr(B.transaction_date,1,10),A.station_id,A.product_code,A.item_description;


insert OVERWRITE table res_notoilSearch_vip
select substr(B.transaction_date,1,10),A.station_id,A.product_code,A.item_description,SUM(A.item_quantity),SUM(A.item_value)
from transaction_items_notoil_vip as A
JOIN transactions_vip as B
ON B.transaction_id = A.transaction_id
where unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)<24*3600
and unix_timestamp(A.transaction_date)-unix_timestamp(B.transaction_date)>-24*3600
group by substr(B.transaction_date,1,10),A.station_id,A.product_code,A.item_description;

INSERT overwrite table res_notoilSearch
SELECT A.days,A.station_id,B.sku_number,B.name,A.sellNum,A.sellMoney
from  res_notoilSearch as A
join res_sku as B
on B.name = trim(A.item_description);

INSERT overwrite table res_notoilSearch_vip
SELECT A.days,A.station_id,B.sku_number,B.name,A.sellNum,A.sellMoney
from  res_notoilSearch_vip as A
join res_sku as B
on B.name = trim(A.item_description);

insert overwrite table tag_resultb
select 
carduser_id,
customer_id,
name,
mobilephone,
maxstationid,
maxmop,
maxoil,
maxdepartment,
case gender 
when '1' then '男' 
when '2' then '女' 
else null end as gender,

case customer_type
when '1' then '潜在顾客'
when '2' then '首次消费顾客'
when '3' then '潜力顾客'
when '4' then '忠诚顾客'
when '5' then '潜在流失顾客'
when '6' then '沉睡顾客'
else null end as customer_type,

case customer_level
when '1' then '普通客户'
when '10' then '金卡客户'
when '20' then '铂金卡客户'
when '30' then '钻石卡客户'
when '40' then '出租车/专快车'
when '50' then '壳牌中国员工'
else null end as customer_level,

case 
when birthday > '1993-01-17' then '青少年'
when birthday < '1993-01-17' and birthday > '1974-01-17' then '青年'
when birthday < '1974-01-17' and birthday > '1958-01-17' then '中年'
when birthday < '1958-01-17' then '老年'
else null  end as birthday,

case 
when recordtime <= date_sub(CURRENT_DATE,7) then '非新注册会员'
else '新注册会员' end as recordtime,

case 
when lastdeal <= 30 then '成长型会员'
when lastdeal > 30 AND lastdeal < 180 then '流失中会员'
else '不活跃会员' end as lastdeal,

case 
when sumdiscount > 50 then '经常使用优惠券型'
when sumdiscount > 0 and sumdiscount <= 50 then '偶尔使用优惠券型'
else '从未使用优惠券型' end as sumdiscount,

case
when onemonthoilnum > 5 then '近期加油频次高'
when onemonthoilnum > 3 and onemonthoilnum <= 5 then '近期加油频次中'
else '近期加油频次低' end as onemonthoilnum,

case 
when onemonthnotoilmoney > 12 then '近期非油消费金额高'
when onemonthnotoilmoney > 5 and onemonthnotoilmoney <= 12 then '近期非油消费金额中'
else '近期非油消费金额低' end as onemonthnotoilmoney,

case 
when threemonthoilnum > 10 then '短期加油频次高'
when threemonthoilnum > 5 and threemonthoilnum <= 10 then '短期加油频次中'
else '短期加油频次低' end as threemonthoilnum

from tag_resulta;

insert overwrite table tag_result
select A.*,concat_ws(' ',
B.gender,
B.customer_type,
B.customer_level,
B.birthday,
B.recordTime,
B.lastdeal,
B.sumDiscount,
B.oneMonthOilNum,
B.onemonthnotoilmoney,
B.threemonthoilnum),null,null from tag_resulta as A
join tag_resultb as B
on A.carduser_id = B.carduser_id;

insert OVERWRITE table tag_frequency_thirty
select CARDUSERID,count(*)
FROM mid.TBL_ORDERDETAILS WHERE ORDER_DEAL = 4 and SENDTIME > date_sub(CURRENT_DATE,30)
GROUP BY CARDUSERID;

insert overwrite table tag_station_thirty
select a.CARDUSERID,concat_ws(' ',collect_set(a.STATIONCODE))   from 
(SELECT CARDUSERID,STATIONCODE
FROM mid.TBL_ORDERDETAILS where SENDTIME > date_sub(CURRENT_DATE,30)
GROUP BY CARDUSERID,STATIONCODE)as a 
GROUP BY A.CARDUSERID;



insert OVERWRITE table tag_oil_thirty
select a.CARDUSERID,concat_ws(' ',collect_set(a.OILCODE)) from 
(select CARDUSERID,OILCODE
FROM mid.TBL_ORDERDETAILS WHERE LITRE > 0 and SENDTIME > date_sub(CURRENT_DATE,30)
GROUP BY CARDUSERID,OILCODE)a
group by a.CARDUSERID;



insert overwrite table tag_notoilDepartment_thirty
select d.carduser_id,concat_ws(' ',collect_set(d.dept_name)) from
(select A.carduser_id as carduser_id,C.dept_name as dept_name
from transaction_items_notoil_vip as A
JOIN mid.products as B
on A.product_code = B.product_code and B.station_id = 50006 and A.create_shijian > date_sub(CURRENT_DATE,30)
join mid.departments as C
on C.department_id = B.department_id and C.station_id=50006 
group by A.carduser_id,C.dept_name)d
group by d.carduser_id;


insert overwrite table tag_resulta_thirty
select a.*,b.station_id,c.max_oil,d.max_department,e.frequency
from tag_station_thirty as b  
left join tag_people as a
on a.carduser_id = b.carduser_id
left join tag_oil_thirty as c 
on c.carduser_id = a.carduser_id
left join tag_notoilDepartment_thirty as d 
on d.carduser_id = a.carduser_id
left join tag_frequency_thirty as e
on a.carduser_id = e.carduser_id;


insert overwrite table tag_resultb_thirty
select 
carduser_id,
customer_id,
name,
mobilephone,
maxstationid,
maxoil,
maxdepartment,
case gender 
when '1' then '男' 
when '2' then '女' 
else null end as gender,

case customer_type
when '1' then '潜在顾客'
when '2' then '首次消费顾客'
when '3' then '潜力顾客'
when '4' then '忠诚顾客'
when '5' then '潜在流失顾客'
when '6' then '沉睡顾客'
else null end as customer_type,

case customer_level
when '1' then '普通客户'
when '10' then '金卡客户'
when '20' then '铂金卡客户'
when '30' then '钻石卡客户'
when '40' then '出租车/专快车'
when '50' then '壳牌中国员工'
else null end as customer_level,

case 
when birthday > '1993-01-17' then '青少年'
when birthday < '1993-01-17' and birthday > '1974-01-17' then '青年'
when birthday < '1974-01-17' and birthday > '1958-01-17' then '中年'
when birthday < '1958-01-17' then '老年'
else null  end as birthday,

case 
when recordtime <= date_sub(CURRENT_DATE,7) then '非新注册会员'
else '新注册会员' end as recordtime
from tag_resulta_thirty;



insert overwrite table tag_result_thirty
select A.*,concat_ws(' ',
B.gender,
B.customer_type,
B.customer_level,
B.birthday,
B.recordTime),null,null from tag_resulta_thirty as A
join tag_resultb_thirty as B
on A.carduser_id = B.carduser_id;

insert OVERWRITE table tag_frequency_sixty
select CARDUSERID,count(*)
FROM mid.TBL_ORDERDETAILS WHERE ORDER_DEAL = 4 and SENDTIME > date_sub(CURRENT_DATE,60)
GROUP BY CARDUSERID;

insert overwrite table tag_station_sixty
select a.CARDUSERID,concat_ws(' ',collect_set(a.STATIONCODE))   from 
(SELECT CARDUSERID,STATIONCODE
FROM mid.TBL_ORDERDETAILS where SENDTIME > date_sub(CURRENT_DATE,60)
GROUP BY CARDUSERID,STATIONCODE)as a 
GROUP BY A.CARDUSERID;



insert OVERWRITE table tag_oil_sixty
select a.CARDUSERID,concat_ws(' ',collect_set(a.OILCODE)) from 
(select CARDUSERID,OILCODE
FROM mid.TBL_ORDERDETAILS WHERE LITRE > 0 and SENDTIME > date_sub(CURRENT_DATE,60)
GROUP BY CARDUSERID,OILCODE)a
group by a.CARDUSERID;



insert overwrite table tag_notoilDepartment_sixty
select d.carduser_id,concat_ws(' ',collect_set(d.dept_name)) from
(select A.carduser_id as carduser_id,C.dept_name as dept_name
from transaction_items_notoil_vip as A
JOIN mid.products as B
on A.product_code = B.product_code and B.station_id = 50006 and A.create_shijian > date_sub(CURRENT_DATE,60)
join mid.departments as C
on C.department_id = B.department_id and C.station_id=50006 
group by A.carduser_id,C.dept_name)d
group by d.carduser_id;


insert overwrite table tag_resulta_sixty
select a.*,b.station_id,c.max_oil,d.max_department,e.frequency
from tag_station_sixty as b  
left join tag_people as a
on a.carduser_id = b.carduser_id
left join tag_oil_sixty as c 
on c.carduser_id = a.carduser_id
left join tag_notoilDepartment_sixty as d 
on d.carduser_id = a.carduser_id
left join tag_frequency_sixty as e
on a.carduser_id = e.carduser_id;


insert overwrite table tag_resultb_sixty
select 
carduser_id,
customer_id,
name,
mobilephone,
maxstationid,
maxoil,
maxdepartment,
case gender 
when '1' then '男' 
when '2' then '女' 
else null end as gender,
case customer_type
when '1' then '潜在顾客'
when '2' then '首次消费顾客'
when '3' then '潜力顾客'
when '4' then '忠诚顾客'
when '5' then '潜在流失顾客'
when '6' then '沉睡顾客'
else null end as customer_type,
case customer_level
when '1' then '普通客户'
when '10' then '金卡客户'
when '20' then '铂金卡客户'
when '30' then '钻石卡客户'
when '40' then '出租车/专快车'
when '50' then '壳牌中国员工'
else null end as customer_level,
case 
when birthday > '1993-01-17' then '青少年'
when birthday < '1993-01-17' and birthday > '1974-01-17' then '青年'
when birthday < '1974-01-17' and birthday > '1958-01-17' then '中年'
when birthday < '1958-01-17' then '老年'
else null  end as birthday,
case 
when recordtime <= date_sub(CURRENT_DATE,7) then '非新注册会员'
else '新注册会员' end as recordtime
from tag_resulta_sixty;



insert overwrite table tag_result_sixty
select A.*,concat_ws(' ',
B.gender,
B.customer_type,
B.customer_level,
B.birthday,
B.recordTime),null,null from tag_resulta_sixty as A
join tag_resultb_sixty as B
on A.carduser_id = B.carduser_id;

insert OVERWRITE table tag_frequency_ninety
select CARDUSERID,count(*)
FROM mid.TBL_ORDERDETAILS WHERE ORDER_DEAL = 4 and SENDTIME > date_sub(CURRENT_DATE,90)
GROUP BY CARDUSERID;

insert overwrite table tag_station_ninety
select a.CARDUSERID,concat_ws(' ',collect_set(a.STATIONCODE))   from 
(SELECT CARDUSERID,STATIONCODE
FROM mid.TBL_ORDERDETAILS where SENDTIME > date_sub(CURRENT_DATE,90)
GROUP BY CARDUSERID,STATIONCODE)as a 
GROUP BY A.CARDUSERID;



insert OVERWRITE table tag_oil_ninety
select a.CARDUSERID,concat_ws(' ',collect_set(a.OILCODE)) from 
(select CARDUSERID,OILCODE
FROM mid.TBL_ORDERDETAILS WHERE LITRE > 0 and SENDTIME > date_sub(CURRENT_DATE,90)
GROUP BY CARDUSERID,OILCODE)a
group by a.CARDUSERID;



insert overwrite table tag_notoilDepartment_ninety
select d.carduser_id,concat_ws(' ',collect_set(d.dept_name)) from
(select A.carduser_id as carduser_id,C.dept_name as dept_name
from transaction_items_notoil_vip as A
JOIN mid.products as B
on A.product_code = B.product_code and B.station_id = 50006 and A.create_shijian > date_sub(CURRENT_DATE,90)
join mid.departments as C
on C.department_id = B.department_id and C.station_id=50006 
group by A.carduser_id,C.dept_name)d
group by d.carduser_id;


insert overwrite table tag_resulta_ninety
select a.*,b.station_id,c.max_oil,d.max_department,e.frequency
from tag_station_ninety as b  
left join tag_people as a
on a.carduser_id = b.carduser_id
left join tag_oil_ninety as c 
on c.carduser_id = a.carduser_id
left join tag_notoilDepartment_ninety as d 
on d.carduser_id = a.carduser_id
left join tag_frequency_ninety as e
on a.carduser_id = e.carduser_id;



insert overwrite table tag_resultb_ninety
select 
carduser_id,
customer_id,
name,
mobilephone,
maxstationid,
maxoil,
maxdepartment,
case gender 
when '1' then '男' 
when '2' then '女' 
else null end as gender,
case customer_type
when '1' then '潜在顾客'
when '2' then '首次消费顾客'
when '3' then '潜力顾客'
when '4' then '忠诚顾客'
when '5' then '潜在流失顾客'
when '6' then '沉睡顾客'
else null end as customer_type,
case customer_level
when '1' then '普通客户'
when '10' then '金卡客户'
when '20' then '铂金卡客户'
when '30' then '钻石卡客户'
when '40' then '出租车/专快车'
when '50' then '壳牌中国员工'
else null end as customer_level,
case 
when birthday > '1993-01-17' then '青少年'
when birthday < '1993-01-17' and birthday > '1974-01-17' then '青年'
when birthday < '1974-01-17' and birthday > '1958-01-17' then '中年'
when birthday < '1958-01-17' then '老年'
else null  end as birthday,
case 
when recordtime <= date_sub(CURRENT_DATE,7) then '非新注册会员'
else '新注册会员' end as recordtime
from tag_resulta_ninety;



insert overwrite table tag_result_ninety
select A.*,concat_ws(' ',
B.gender,
B.customer_type,
B.customer_level,
B.birthday,
B.recordTime),null,null from tag_resulta_ninety as A
join tag_resultb_ninety as B
on A.carduser_id = B.carduser_id;

insert into tag_result_thirty
select a.carduser_id,
a.customer_id,
a.customer_type,
a.customer_level,
a.gender,
a.mobileCarType,
a.name,
a.birthday ,
a.recordTime ,
a.mobilePhone ,
a.address ,
a.balance ,
null ,
null,
null,
null,
null,
null,null
from tag_result as a
left outer join tag_result_thirty as b
on a.carduser_id = b.carduser_id
where b.carduser_id is null;

insert into tag_result_sixty
select a.carduser_id,
a.customer_id,
a.customer_type,
a.customer_level,
a.gender,
a.mobileCarType,
a.name,
a.birthday ,
a.recordTime ,
a.mobilePhone ,
a.address ,
a.balance ,
null ,
null,
null,
null,null,
null,null
from tag_result as a
left outer join tag_result_sixty as b
on a.carduser_id = b.carduser_id
where b.carduser_id is null;

insert into tag_result_ninety
select a.carduser_id,
a.customer_id,
a.customer_type,
a.customer_level,
a.gender,
a.mobileCarType,
a.name,
a.birthday ,
a.recordTime ,
a.mobilePhone ,
a.address ,
a.balance ,
null ,
null,
null,
null,null,
null,null
from tag_result as a
left outer join tag_result_ninety as b
on a.carduser_id = b.carduser_id
where b.carduser_id is null;
